﻿using AutoMapper;
using AutoMapper.Execution;
using GuanYijia.Application.IntPut;
using GuanYijia.Application.OutPut;
using GuanYijia.DoMain;
using GuanYijia.DoMain.PageDto;
using GuanYijia.EntityFarmeworkCore;
using GuanYijia.Repository;
using GuanYiJia.WebApi;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.StaticFiles;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Asn1.Ocsp;
using Panda.DynamicWebApi;
using Panda.DynamicWebApi.Attributes;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace GuanYijia.Application.ProductManagement
{
    [DynamicWebApi]
    [ApiExplorerSettings(GroupName = "v1")]
    public class ProductDataService : ControllerBase, IDynamicWebApi
    {
        private readonly IBaseRepository<ProductInfo> _productInfoRepository;
        private readonly IBaseRepository<Product_Attribute> _attributeRepository;
        private readonly IBaseRepository<Product_Current_Stock> _currentstockRepository;
        private readonly IBaseRepository<Product_Initial_Stock> _initialstockRepository;
        private readonly IBaseRepository<Product_Price> _priceRepository;
        private readonly IBaseRepository<MaterialProperty> _propertyRepository;
        private readonly IBaseRepository<MaterialCategory> _categoryRepository;
        private readonly IMapper _mapper;
        private readonly GuanYiJiaDbContext context;
        public ProductDataService(IBaseRepository<ProductInfo> productInfoRepository, IBaseRepository<Product_Attribute> attributeRepository, IBaseRepository<Product_Current_Stock> currentstockRepository, IBaseRepository<Product_Initial_Stock> initialstockRepository, IBaseRepository<Product_Price> priceRepository, IBaseRepository<MaterialProperty> propertyRepository, IBaseRepository<MaterialCategory> categoryRepository, IMapper mapper, GuanYiJiaDbContext context)
        {
            _productInfoRepository = productInfoRepository;
            _attributeRepository = attributeRepository;
            _currentstockRepository = currentstockRepository;
            _initialstockRepository = initialstockRepository;
            _priceRepository = priceRepository;
            _propertyRepository = propertyRepository;
            _categoryRepository = categoryRepository;
            _mapper = mapper;
            this.context = context;
        }
        [SkipCustomFilterAttribute]
        [HttpPost]
        public async Task<PageResultDto<ProductListDto>> GetProductShow(ProductShowConditionDto request)
        {
            var list = new PageResultDto<ProductListDto>() { TotalCount = 0, TotalPage = 0, Data = new List<ProductListDto>() };

            var pageIndexParam = new MySqlParameter("@pageIndex", request.pageIndex);
            var pageSizeParam = new MySqlParameter("@pageSize", request.pageSize);
            var keywordsParam = new MySqlParameter("@keywords", string.IsNullOrWhiteSpace(request.keywords) ? (object)DBNull.Value : request.keywords);
            var categoryIdParam = new MySqlParameter("@category_id", request.category_id.HasValue ? (object)request.category_id.Value : DBNull.Value);
            var colorParam = new MySqlParameter("@color", string.IsNullOrWhiteSpace(request.color) ? (object)DBNull.Value : request.color);
            var enabledParam = new MySqlParameter("@enabled", request.enabled.HasValue ? (object)request.enabled.Value : DBNull.Value);
            var cserial_numberParam = new MySqlParameter("@serial_number", request.serial_number.HasValue ? (object)request.serial_number.Value : DBNull.Value);
            var mfrsParam = new MySqlParameter("@mfrs", string.IsNullOrWhiteSpace(request.mfrs) ? (object)DBNull.Value : request.mfrs);
            var remarkParam = new MySqlParameter("@remark", string.IsNullOrWhiteSpace(request.remark) ? (object)DBNull.Value : request.remark);
            var safety_stockParam = new MySqlParameter("@safety_stock", request.safety_stock.HasValue ? (object)request.safety_stock.Value : DBNull.Value);
            try
            {
                // 使用原始 SQL 查询获取 DTO
                var result = await context.Set<ProductListDto>()
                    .FromSqlRaw("CALL GetAllProducts(@pageIndex, @pageSize, @keywords, @category_id,@color,@enabled,@serial_number,@mfrs,@remark,@safety_stock)",
                                 pageIndexParam, pageSizeParam, keywordsParam, categoryIdParam, colorParam, enabledParam, cserial_numberParam, mfrsParam, remarkParam, safety_stockParam)
                    .ToListAsync();
                // 设置返回的数据
                list.Data = result;
                list.TotalCount = result.Count();
            }
            catch (Exception ex)
            {
                // 处理异常，可以记录日志或返回特定的错误信息
                throw new Exception("An error occurred while retrieving products.", ex);
            }
            return list;
        }

        public async Task<ResponseModelDto> AddProductAndOther(AddProductRequestDto request)
        {
            ResponseModelDto response = new ResponseModelDto();
            var listOne = await _productInfoRepository.GetAllAsync(p => p.product_name == request.productInfo.product_name && !p.delete_flag && p.category_id == request.productInfo.category_id);
            if (listOne.Count > 0)
            {
                response.Code = -1;
                response.Message = "同一商品类型下已存在重复的商品名称";
                return response;
            }




            var time = DateTime.Now;
            #region 添加商品
            ProductInfo info = new ProductInfo();
            info.category_id = request.productInfo.category_id;
            info.product_name = request.productInfo.product_name;
            info.mfrs = request.productInfo.mfrs;
            info.safety_stock = request.productInfo.safety_stock;
            info.model = request.productInfo.model;
            info.standard = request.productInfo.standard;
            info.color = request.productInfo.color;
            info.unit = request.productInfo.unit;
            info.remark = request.productInfo.remark;
            info.img_name = request.productInfo.img_name;
            info.enabled = request.productInfo.enabled;
            info.enable_serial_number = request.productInfo.enable_serial_number;
            info.tenant_id = request.productInfo.tenant_id;
            info.delete_flag = false;
            await _productInfoRepository.InsertAsync(info);
            response.Code++;
            #endregion

            #region 添加价格
            Product_Price price = new Product_Price();
            price.bar_code = request.product_Price.bar_code;
            price.productid = info.productid;
            price.purchase_decimal = request.product_Price.purchase_decimal;
            price.commodity_decimal = request.product_Price.commodity_decimal;
            price.wholesale_decimal = request.product_Price.wholesale_decimal;
            price.low_decimal = request.product_Price.low_decimal;
            price.create_time = time;
            price.tenant_id = request.product_Price.tenant_id;
            price.delete_flag = false;
            await _priceRepository.InsertAsync(price);
            response.Code++;
            #endregion

            #region 添加期初库存
            Product_Initial_Stock initial_Stock = new Product_Initial_Stock();
            initial_Stock.product_id = info.productid;
            initial_Stock.depot_id = request.product_Initial_Stock.depot_id;
            initial_Stock.number = request.product_Initial_Stock.number;
            initial_Stock.tenant_id = request.product_Initial_Stock.tenant_id;
            initial_Stock.delete_flag = false;
            await _initialstockRepository.InsertAsync(initial_Stock);
            response.Code++;
            #endregion

            #region 添加期末库存
            Product_Current_Stock current_Stock = new Product_Current_Stock();
            current_Stock.product_id = info.productid;
            current_Stock.depot_id = request.product_Current_Stock.depot_id;
            current_Stock.current_number = request.product_Current_Stock.current_number;
            current_Stock.tenant_id = request.product_Current_Stock.tenant_id;
            current_Stock.delete_flag = false;
            await _currentstockRepository.InsertAsync(current_Stock);
            response.Code++;
            #endregion
            response.Message = "添加成功";
            return response;
        }




        /// <summary>
        /// 商品信息反填
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        [SkipCustomFilterAttribute]
        [HttpGet]
        public async Task<object> GetProductOne(int productId)
        {
            List<ProductUpdateInfoDto> list = new List<ProductUpdateInfoDto>();
            var pageIndexParam = new MySqlParameter("@pageIndex", 1);
            var pageSizeParam = new MySqlParameter("@pageSize", 1);
            var productidParam = new MySqlParameter("@productid", productId);
            try
            {
                // 使用原始 SQL 查询获取 DTO
                var result = await context.Set<ProductUpdateInfoDto>()
                    .FromSqlRaw("CALL ProductUpdateById(@pageIndex, @pageSize, @productid)",
                                 pageIndexParam, pageSizeParam, productidParam)
                    .ToListAsync();
                // 设置返回的数据
                list = result;
            }
            catch (Exception ex)
            {
                // 处理异常，可以记录日志或返回特定的错误信息
                throw new Exception("An error occurred while retrieving products.", ex);
            }
            return list;
        }
        /// <summary>
        /// 修改商品信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ResponseModelDto> UpdateProductAndOther(AddProductRequestDto request)
        {
            ResponseModelDto response = new ResponseModelDto();
            var listOne = await _productInfoRepository.GetAllAsync(p => p.product_name == request.productInfo.product_name && !p.delete_flag && p.productid
            != request.productInfo.productid && p.category_id == request.productInfo.category_id);
            if (listOne.Count > 0)
            {
                response.Code = -1;
                response.Message = "同一商品类型下已存在重复的商品名称";
                return response;
            }
            var time = DateTime.Now;
            #region 修改商品信息
            var info = await _productInfoRepository.GetAsync(p => p.productid == request.productInfo.productid);
            info.category_id = request.productInfo.category_id;
            info.product_name = request.productInfo.product_name;
            info.mfrs = request.productInfo.mfrs;
            info.safety_stock = request.productInfo.safety_stock;
            info.model = request.productInfo.model;
            info.standard = request.productInfo.standard;
            info.color = request.productInfo.color;
            info.unit = request.productInfo.unit;
            info.remark = request.productInfo.remark;
            info.img_name = request.productInfo.img_name;
            info.enabled = request.productInfo.enabled;
            info.enable_serial_number = request.productInfo.enable_serial_number;
            await _productInfoRepository.UpdateAsync(info);
            response.Code++;
            #endregion

            #region 修改价格
            var price = await _priceRepository.GetAsync(p => p.productid == request.productInfo.productid);
            price.productid = info.productid;
            price.purchase_decimal = request.product_Price.purchase_decimal;
            price.commodity_decimal = request.product_Price.commodity_decimal;
            price.wholesale_decimal = request.product_Price.wholesale_decimal;
            price.low_decimal = request.product_Price.low_decimal;
            price.update_time = time;
            await _priceRepository.UpdateAsync(price);
            response.Code++;
            #endregion

            #region 修改期初库存
            var initial_Stock = await _initialstockRepository.GetAsync(p => p.product_id == request.productInfo.productid);
            initial_Stock.number = request.product_Initial_Stock.number;
            await _initialstockRepository.UpdateAsync(initial_Stock);
            response.Code++;
            #endregion

            #region 修改期末库存
            var current_Stock = await _currentstockRepository.GetAsync(p => p.product_id == request.productInfo.productid);
            current_Stock.current_number = request.product_Current_Stock.current_number;
            await _currentstockRepository.UpdateAsync(current_Stock);
            response.Code++;
            #endregion

            //using (var tran = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions
            //{
            //    IsolationLevel = IsolationLevel.Serializable
            //}))
            //{
            //    try
            //    {
            //        lock (tran)
            //        {
            //            
            //        }
            //    }
            //    catch (Exception ex)
            //    {

            //        throw;
            //    }
            //}
            response.Message = "修改成功";
            return response;
        }

        /// <summary>
        /// 商品类型批量删除-单独删除
        /// </summary>
        /// <param name="isd"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<ResponseModelDto> DeleteProductRange(List<int> isd)
        {
            ResponseModelDto response = new ResponseModelDto();
            var productList = await _productInfoRepository.GetAllAsync(p => isd.Contains(p.productid));
            if (productList.Where(p => p.enabled).ToList().Count > 0)
            {
                response.Code = -1;
                response.Message = "存在状态正在启用的商品，请先在首列编辑中修改商品状态";
                return response;
            }
            productList = await _productInfoRepository.GetAllAsync(p => isd.Contains(p.productid));
            foreach (var item in productList)
            {
                item.delete_flag = true;
            }
            var list = productList.ToList();
            response.Code = await _productInfoRepository.UpdateRangeAsync(list);
            response.Message = "删除成功";
            return response;
        }

        [HttpPost]
        public async Task<object> ImportExcel(IFormFile file)
        {
            try
            {
                //相对路径
                string path = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "uploads", file.FileName);
                //using 作用

                using (var stream = new FileStream(path, FileMode.Create))
                {
                    file.CopyTo(stream);
                }
                return 1;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 导出Excel表
        /// </summary>
        /// <returns>导出Excel表</returns>
        [HttpGet]
        public async Task<object> ExportExcel()
        {
            byte[] data = null;
            string contentType = new FileExtensionContentTypeProvider().Mappings[".xlsx"];
            string fileName = "商品列表导出" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

            IWorkbook book = new XSSFWorkbook();
            ISheet sheet = book.CreateSheet();
            IRow row1 = sheet.CreateRow(0);
            row1.CreateCell(0).SetCellValue("条码");
            row1.CreateCell(1).SetCellValue("名称");
            row1.CreateCell(2).SetCellValue("规格");
            row1.CreateCell(3).SetCellValue("型号");
            row1.CreateCell(4).SetCellValue("颜色");
            row1.CreateCell(5).SetCellValue("类别");
            row1.CreateCell(6).SetCellValue("扩展信息");
            row1.CreateCell(7).SetCellValue("单位");
            row1.CreateCell(8).SetCellValue("库存");
            row1.CreateCell(9).SetCellValue("采购价");
            row1.CreateCell(10).SetCellValue("零售价");
            row1.CreateCell(11).SetCellValue("销售价");
            row1.CreateCell(12).SetCellValue("最低售价");
            row1.CreateCell(13).SetCellValue("基础重量");
            row1.CreateCell(14).SetCellValue("备注");
            row1.CreateCell(15).SetCellValue("状态");

            var list = from a in await _productInfoRepository.GetAllAsync()
                       join b in await _currentstockRepository.GetAllAsync() on a.productid equals b.product_id
                       join c in await _priceRepository.GetAllAsync() on a.productid equals c.productid
                       join e in await _categoryRepository.GetAllAsync() on a.category_id equals e.category_id
                       select new
                       {
                           productid = a.productid,
                           bar_code = c.bar_code,
                           product_name = a.product_name,
                           category_id = a.category_id,
                           standard = a.standard,
                           model = a.model,
                           color = a.color,
                           category_name = e.name,
                           parent_id = e.parent_id,
                           unit = a.unit,
                           mfrs = a.mfrs,
                           current_number = b.current_number,
                           purchase_decimal = c.purchase_decimal,
                           commodity_decimal = c.commodity_decimal,
                           wholesale_decimal = c.wholesale_decimal,
                           low_decimal = c.low_decimal,
                           enabled = a.enabled,
                           enable_serial_number = a.enable_serial_number,
                           safety_stock = a.safety_stock,
                           remark = a.remark
                       };

            int index = 1;
            foreach (var item in list.ToList())
            {
                IRow row = sheet.CreateRow(index);
                row.CreateCell(0).SetCellValue(item.bar_code);
                row.CreateCell(1).SetCellValue(item.product_name);
                row.CreateCell(2).SetCellValue(item.standard == null ? "" : item.standard);
                row.CreateCell(3).SetCellValue(item.model == null ? "" : item.model);
                row.CreateCell(4).SetCellValue(item.color == null ? "" : item.color);
                row.CreateCell(5).SetCellValue(item.category_name == null ? "" : item.category_name);
                row.CreateCell(6).SetCellValue(item.mfrs == null ? "" : item.mfrs);
                row.CreateCell(7).SetCellValue(item.unit == null ? "" : item.unit);
                row.CreateCell(8).SetCellValue(item.current_number == null ? "" : item.current_number.Value.ToString("0.00"));
                row.CreateCell(9).SetCellValue(item.purchase_decimal == null ? "" : item.purchase_decimal.Value.ToString("0.00"));
                row.CreateCell(10).SetCellValue(item.commodity_decimal == null ? "" : item.commodity_decimal.Value.ToString("0.00"));
                row.CreateCell(11).SetCellValue(item.wholesale_decimal == null ? "" : item.wholesale_decimal.Value.ToString("0.00"));
                row.CreateCell(12).SetCellValue(item.low_decimal == null ? "" : item.low_decimal.Value.ToString("0.00"));
                row.CreateCell(13).SetCellValue(item.safety_stock == null ? "" : item.safety_stock.Value.ToString("0.00"));
                row.CreateCell(14).SetCellValue(item.remark == null ? "" : item.remark);
                row.CreateCell(15).SetCellValue(item.enabled == true ? "启用" : "禁用");
                index++;
            }

            using (MemoryStream ms = new MemoryStream())
            {
                book.Write(ms);
                data = ms.ToArray();
            }
            //new { data, contentType, fileName }
            return File(data, contentType, fileName);
        }
    }

}
